<?php
class Model_UserClient extends Model_User
{
  public function getUser($id)
  {
    $select  = $this->db->select()->from('acc_client')->where('client_id = ?', $id);
    $user = $this->db->fetchRow($select);
    //App_Log::query($this->db);
    if (!$user) {
      throw new Exception("user [$id] not found!");
    }
    return $user;
  }
	
	/*public function getClientIDByService($dpt_id, $dpt_account_login)
	{
		$select  = $this->db->select()->from('acc_client_service', array('client_id'));
		$select->where('dpt_id = ?', (int)$dpt_id);
		$select->where('dpt_account_login = ?', $dpt_account_login);

		$client_id = $this->db->fetchOne($select);
		//App_Log::query($this->db);

		return $client_id;
	}*/
	
	public function dptLoginCheck($dpt_id, $dpt_account_login, $password)
	{
		$login_status = false;

		$model_service = Model_ServiceFactory::create($dpt_id);
		//Zend_Debug::dump($model_service);
		if (!$model_service){ 
			return false;   // no service model
		}
	
    	$check_rs = $model_service->loginCheck($dpt_id, $dpt_account_login, $password);
		//Zend_Debug::dump($check_rs);exit;
		return $check_rs;
	}

	public function dptYesupAccount($dpt_id, $dpt_account_login)
	{
		$login_status = false;

		$model_service = Model_ServiceFactory::create($dpt_id);
		//Zend_Debug::dump($model_service);
		if (!$model_service){ 
			return false;   // no service model
		}
    	
		$check_rs = $model_service->yesupAccount($dpt_id, $dpt_account_login);
		//Zend_Debug::dump($check_rs);exit;
		return $check_rs;
	}
	
	public function getClient($client_id,$allow_pending='no')
	{
		$select  = $this->db->select()->from('acc_client');
		$select->where('client_id = ?', (string)$client_id);
		if ($allow_pending=='no')
			$select->where('status = ?', 'Active');

		$client = $this->db->fetchRow($select);
		//App_Log::query($this->db);

		return $client;
	}
	
	public function getClientByEmail($email, $password="")
	{
		$select  = $this->db->select()->from('acc_client');
		$select->where('email = ?', (string)$email);
		if ($password) $select->where('password = ?', md5($password));

		$client = $this->db->fetchRow($select);
		//App_Log::query($this->db);

		return $client;
	}

	public function getAllClient($data)
	{
		//Zend_Debug::dump($data);exit;
		
		$grid_ft = new Model_GridFilter($data);
		
		//for dpt manager search only
		if ( ( isset($data['filters'][4]['field']) and ($data['filters'][4]['data']['value']=='manager') )
			and (  (isset($data['filters'][1]['field'])) and ($data['filters'][1]['data']['value']!=-1) ) )
		{
			$dpt_id=$data['filters'][1]['data']['value'];
			$ft_fields = array('email'=>'a',
							   'dpt_account_login'=>'s',
							   'login_area'=>'s',
							   'role'=>'a'
								);
			
	        $select = $this->db->select()->from(array('a'=>'acc_client'))
	                  ->joinLeft(array('s'=>'acc_client_service'),'a.client_id=s.client_id')
	                  ->joinLeft(array('m'=>'acc_managers'),'a.client_id=m.client_id')
	                  ->group('a.client_id');
	                  
	        $select = $grid_ft->addFilter($select, $ft_fields);
	
	        $total = count($this->db->fetchAll($select));
								
	        $select = $this->db->select()->from(array('a'=>'acc_client'))
	                  ->joinLeft(array('s'=>'acc_client_service'),'a.client_id=s.client_id',array('dpt_account_login','login_area'))
	                  ->joinLeft(array('m'=>'acc_managers'),'a.client_id=m.client_id',array('dpt_ids'));
	                  $select = $grid_ft->addFilter($select, $ft_fields);
	                  $select->where('m.dpt_ids like ?','%'.$dpt_id.'%');
	                  $select->group('a.client_id');
	                  $select->order($grid_ft->getOrder($ft_fields, 'a.create_time desc'))
	                  ->limit($grid_ft->getLimit(), $grid_ft->getStart());
	        //echo $select;
	                  
		}else{ // for other search
		
			$ft_fields = array('email'=>'a',
							   'dpt_id'=>'s',
							    'dpt_account_login'=>'s',
								'login_area'=>'s',
								'role'=>'a'
								);
			
	        $select = $this->db->select()->from(array('a'=>'acc_client'))
	                  ->joinLeft(array('s'=>'acc_client_service'),'a.client_id=s.client_id')
	                  ->group('a.client_id');
	                  
	        $select = $grid_ft->addFilter($select, $ft_fields);
	
	        $total = count($this->db->fetchAll($select));
								
	        $select = $this->db->select()->from(array('a'=>'acc_client'))
	                  ->joinLeft(array('s'=>'acc_client_service'),'a.client_id=s.client_id',array('dpt_id', 'dpt_account_login','login_area'));
	                  $select = $grid_ft->addFilter($select, $ft_fields);
	                  $select->group('a.client_id');
	                  $select->order($grid_ft->getOrder($ft_fields, 'a.create_time desc'))
	                  ->limit($grid_ft->getLimit(), $grid_ft->getStart());
	        //echo $select;
		}
                  
		$clients = $this->db->fetchAll($select);
		
		//App_Log::query($this->db);
		$results=array();
		foreach($clients as $key => $value ){
			$client_id = $value['client_id'];
			$results[ $client_id ]['services'] = $this->getClientService($client_id);
			$results[ $client_id ]['email'] = $value['email'];
			$results[ $client_id ]['role'] = $value['role'];
			$results[ $client_id ]['status'] = $value['status'];
		}
		
		$rs=array();
		$rs['total']=$total;
		$rs['data']=$results;
		return $rs;
	}
	
	public function linkClientService($client_id, $dpt_id, $dpt_account_id, $dpt_login_arr)
	{
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
    
		$new_client = false;
		if (!$client_id)
		{
			$client_id = strtolower(md5(uniqid(rand(), true)));
			$new_client = true;
		}

		//$email = $dpt_login_arr['email'];
		$data_client = array(
            'client_id' => $client_id,
            'role' => 'client',
            'status' => 'Active',
            'create_time' => $now,
            'update_time' => $now,
		);

		$this->db->beginTransaction();
		try {
			//Zend_Debug::dump($new_client);
			
			if ($new_client) $this->db->insert('acc_client',$data_client);
			
			$dpt_account_login = $dpt_login_arr['dpt_account_login'];
			$login_area = $dpt_login_arr['login_area'];
			
			// only emailserving's password need to be stored
			if (isset($dpt_login_arr['password']) && $dpt_id == 3006) $dpt_password = $dpt_login_arr['password'];
			else $dpt_password = "";
			
			$sql = "INSERT IGNORE INTO acc_client_service (
									`link_id` ,
									`client_id` ,
									`dpt_id` ,
									`dpt_account_id` ,
									`dpt_account_login`,
									`dpt_password`,
									`login_area`,
									`create_time` ,
									`update_time` 
									)
									VALUES (
									NULL , '$client_id', '$dpt_id', '$dpt_account_id', '$dpt_account_login', '$dpt_password', '$login_area', '$now', '$now')";

			$this->db->query($sql);
			$this->db->commit();
			
			//for action log
			$data=array();
			$data['ip'] = Model_ActionLog::getIp();			
			$data['user_id'] = isset($dpt_login_arr['admin_user_id'])?$dpt_login_arr['admin_user_id']:"";
			$data['client_id'] = $client_id;
			$data['dpt_id'] = $dpt_id;
			$data['dpt_account_id'] = $dpt_account_id;
			$data['action'] = 'add service';
			$data['memo'] = isset($dpt_login_arr['url'])?$dpt_login_arr['url']:"";			
			Model_ActionLog::actionLog($data);
		}
		catch (Zend_Exception $e){
			$this->db->rollBack();
			//throw new Zend_Exception("Transaction failed, and roll back.");
			
			//error_log("Transaction failed, and roll back.");
			return false;
		}  
		//App_Log::query($this->db);

		return $client_id;
	}
	
	public function updateClientService($client_id, $dpt_id, $dpt_account_id, $dpt_login_arr)
	{
		//$dpt_acount_login = $dpt_login_arr['dpt_account_login'];
		$login_area = $dpt_login_arr['login_area'];
		
		$where[] = $this->db->quoteInto('client_id = ?', $client_id);
		$where[] = $this->db->quoteInto('dpt_id = ?', $dpt_id);
		$where[] = $this->db->quoteInto('login_area = ?', $login_area);
		
		$data_update['dpt_account_id'] = $dpt_account_id;
		
		$num = $this->db->update('acc_client_service', $data_update, $where);
		$updated = array('affected'=>$num);
		
		return $updated;
	}

	
	public function signupClient($email,$password, $id_type='yesup')
	{
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
    
		$client_id = strtolower(md5(uniqid(rand(), true)));
		if (!$password) $passwd = '';
		else $passwd = md5($password);
		
		if ($id_type='google'){
			$status='Active';
		}else{
			$status='Pending';
		}
		
		$data_client = array(
            'client_id' => $client_id,
            'role' => 'client',
			'email' => $email,
			'password' => $passwd,
			'id_type' => $id_type,
            'status' => $status,
            'create_time' => $now,
            'update_time' => $now,
		);

		try {
			$this->db->insert('acc_client',$data_client);
		}catch (Zend_Exception $e){
			//throw new Zend_Exception("Signup failed, data error or email already exist in system.");	
			//return false;		
			$client_id = -1;
		}			
		return $client_id;
	}
	

	public function remoteSignupClient($email,$password, $id_type='yesup')
	{
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
    
		$client_id = strtolower(md5(uniqid(rand(), true)));
		if (!$password) $passwd = '';
		else $passwd = md5($password);
		
		$data_client = array(
            'client_id' => $client_id,
            'role' => 'client',
			'email' => $email,
			'password' => $passwd,
			'id_type' => $id_type,
            'status' => 'Active',
            'create_time' => $now,
            'update_time' => $now,
		);

		try {
			$this->db->insert('acc_client',$data_client);
		}catch (Zend_Exception $e){
			//throw new Zend_Exception("Signup failed, data error or email already exist in system.");	
			//return false;		
			$client_id = -1;
		}			
		return $client_id;
	}
	
	
	public function activateClient($client_id)
	{
	    $select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id)->where('status = ?', 'Pending');
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return false;
	    }
		
		$sql = " update acc_client set status='Active' where client_id='$client_id' and status='Pending' ";
		$this->db->query($sql);
		return true;
	}

	public function suspendClient($client_id)
	{
	    $select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id);
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return false;
	    }
		
		$sql = " update acc_client set status='Suspend' where client_id='$client_id' ";
		$this->db->query($sql);
		return true;
	}
	
	public function disableClient($client_id)
	{
	    $select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id);
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return false;
	    }
		
		$sql = " update acc_client set status='Disable' where client_id='$client_id' ";
		$this->db->query($sql);
		return true;
	}
	
	public function udpatePassword($email,$password)
	{
	    $select  = $this->db->select()->from('acc_client')->where('email = ?', $email)->where('status != ?', 'Disable');
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return false;
	    }
		
	    $password=md5($password);
		$sql = " update acc_client set password='$password',status='Active' where email='$email' ";
		$this->db->query($sql);
		return true;
	}	
	
	public function getClientService($client_id, $dpt_id='')
	{
		$select  = $this->db->select()->from(array('a'=>'acc_client_service'));
		$select->joinLeft(array('s'=>'acc_service'),
    								'a.dpt_id=s.dpt_id',
                  			 array('a.dpt_id', 'a.dpt_account_id', 'dpt_name', 'service_url','network_id','icon','description','group_id','group_order'));
		$select->where('client_id = ?', (string)$client_id);
		if ($dpt_id) $select->where('a.dpt_id = ?', $dpt_id);
		//$select->order('dpt_name asc');
		$select->order('group_order asc');
		
		$services = $this->db->fetchAll($select);
		//App_Log::query($this->db);

		return $services;
	}

	public function getClientGroup($client_id)
	{
		$select  = $this->db->select()->from(array('a'=>'acc_client_service'));
		$select->joinLeft(array('s'=>'acc_service'),
    								'a.dpt_id=s.dpt_id',
                  			 array('group_id'));
		$select->where('client_id = ?', (string)$client_id);
		$select->group('group_id');
		$select->order('group_id asc');
		
		$groups = $this->db->fetchAll($select);
		
		
		foreach($groups as $indx => $item){
			$select  = $this->db->select()->from('acc_group')->where('group_id = ?', $item['group_id']);
	    	$info = $this->db->fetchRow($select);
	    	if (isset($info['group_name']))
	    		$groups[$indx]['group_name']=$info['group_name'];
	    	else
	    		$groups[$indx]['group_name']='';			
		}
		//App_Log::query($this->db);

		return $groups;
	}
	
	
	public function removeClientService($user_id, $dpt_id, $dpt_account_id, $client_id,$dpt_login_arr)
	{
		if (($user_id!=$client_id)and(empty($dpt_login_arr['admin_user_id'])))
			return false;  //no privilege
			
		$select  = $this->db->select()->from('acc_client_service');
		$select->where('client_id = ?', (string)$client_id);
		$select->where('dpt_id = ?', (string)$dpt_id);
		$select->where('dpt_account_id = ?', (string)$dpt_account_id);
		$service = $this->db->fetchRow($select);
		
		if ($service){
/*			
			$sql = " update acc_client_service set status='Disable' where client_id='$client_id' and dpt_id='$dpt_id' and dpt_account_id='$dpt_account_id'";
*/			
			$sql = "delete from acc_client_service where client_id='$client_id' and dpt_id='$dpt_id' and dpt_account_id='$dpt_account_id'";
			$this->db->query($sql);
			
			//for action log
			$data=array();
			$data['ip'] = Model_ActionLog::getIp();			
			$data['user_id'] = $dpt_login_arr['admin_user_id'];
			$data['client_id'] = $client_id;
			$data['dpt_id'] = $dpt_id;
			$data['dpt_account_id'] = $dpt_account_id;
			$data['action'] = 'remove service';
			$data['memo'] = $dpt_login_arr['url'];			
			Model_ActionLog::actionLog($data);			
			
			return true;
		}else{
			return false;
		}  

	}
	

	public function getProfile($client_id)
	{
		$select  = $this->db->select()->from('acc_client_profile');
		$select->where('client_id = ?', (string)$client_id);
		$profile = $this->db->fetchRow($select);
		//App_Log::query($this->db);

		return $profile;
	}
	
	
	public function updateProfile($data)
	{
		$client_id = isset($data['client_id']) ? $data['client_id']:'';
		$data_profile = array(
            'client_id' => isset($data['client_id']) ? $data['client_id']:'',
            'first_name' => isset($data['first_name']) ? $data['first_name']:'',
            'last_name' => isset($data['last_name']) ? $data['last_name']:'',
			'email' => isset($data['email']) ? $data['email']:'',
			'company' => isset($data['company']) ? $data['company']:'',
			'phone' => isset($data['phone']) ? $data['phone']:'',
            'address' => isset($data['address']) ? $data['address']:'',
            'city' => isset($data['city']) ? $data['city']:'',
            'state' => isset($data['state']) ? $data['state']:'',
            'zipcode' => isset($data['zipcode']) ? $data['zipcode']:'',
            'country' => isset($data['country']) ? $data['country']:'',
            'timezone_id' => isset($data['timezone_id']) ? $data['timezone_id']:'',
			'website' => isset($data['website']) ? $data['website']:'',
		);

		$select  = $this->db->select()->from('acc_client_profile');
		$select->where('client_id = ?', (string)$client_id);
		$profile = $this->db->fetchRow($select);
		//Zend_Debug::dump($profile);exit;
		
		
		if ($profile){
			$where[] = $this->db->quoteInto('client_id = ?', $client_id);
			$num = $this->db->update('acc_client_profile',$data_profile, $where);
			$updated = array('affected'=>$num);
			if ($updated)
				return true;
			else 
				return false;
		}else{
			$this->db->insert('acc_client_profile',$data_profile);
			if ($this->db->lastInsertId())
				return true;
			else 
				return false;
		}			
	}
	
	
	public function setClientSecurityKey($dpt_id, $client_id)
	{
	    $login_url = "http://admin.clicksor.com/admin/yesupCentralAccount_Clicksor.php";
	    $login_url = "yesupCentralAccount_Clicksor.php";
	    
	    $skey = 'partner_'.$dpt_id.'_'.md5(time().rand());
	    $rs = $this->updateClientKey($client_id, $skey);
	    // for security, this url is hard code to every service backend login scripts
	    if ($rs){
	    	$remote_cal_url = $login_url."?client_id=$client_id&skey=$skey";
	    }else $remote_cal_url = "";
	  
	    return $remote_cal_url;
	}
	
	
	public function updateClientKey($client_id, $skey = "")
	{
		$query = "update acc_client set partner_key = '$skey' 
				  where client_id = '$client_id'  ";
		$updated = $this->db->query($query);
		//App_Log::query($this->db);

		if ($updated) return true;
		else return false;
	}	
		
	public function shareClientService($client_id, $dpt_id, $dpt_account_id, $share_to)
	{
		$valid_email=self::isValidEmail($share_to);
		if (!$valid_email){
			return 'Sorry, please input valid email address.';
		}

	    $select  = $this->db->select()->from('acc_client_service')
	    			->where('client_id = ?', $client_id)
	    			->where('share_from = ?', '')
	    			->where('dpt_id = ?', $dpt_id)
	    			->where('dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchRow($select);
	    if (empty($service)) {
	    	return 'Sorry, failed to share this account because you are not the orignal owner.';
	    }
		
		$select  = $this->db->select()->from('acc_client')->where('email = ?', $share_to);
	    $email_user = $this->db->fetchRow($select);
	    if (empty($email_user)) {
	    	//send an email to inform user to sign up an account.
			$select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id);
	    	$owner = $this->db->fetchRow($select);

	    	$skey = md5(time().rand());
			$key=$this->addShareSecurityKey($client_id, $dpt_id, $dpt_account_id, $share_to, $skey);
			if ($key!=0){
    			$body = "Hi,<br><br>
							A YesUp user ".$owner['email']." has shared an account with you!<br><br>
							Please sign up with YesUp by clicking on the following link:<br><br>
							<a href='https://account.yesup.com/share-signup/index/key/".$key."' target=_blank>https://account.yesup.com/share-signup</a><br><br>
							
							You will be able to access the account after your signup and login. <br><br>
							
							Please feel free to contact us if you have any questions.<br><br>
							
							Best regards,<br><br>
							
							YesUp Account Support Team<br>";
    			
			    $mail = new Zend_Mail('UTF-8');
			    $mail->setBodyHtml($body)
			         ->setFrom('no-reply@yesup.com', 'YesUp Account')
			         ->addTo($share_to, '')
			         ->setSubject("A YesUp user has invited you to share his account!")
			         ->send();    			
	    	
	    		return 'Sorry, the user with this email address has not signed up an account yet. An email will be sent to inform this user.';
			}else{
	    		return 'Sorry, failed to send email to inform this user.';				
			}
	    }
	    
		$select  = $this->db->select()->from('acc_client')->where('email = ?', $share_to)->where('status != ?', 'Disable');
	    $user = $this->db->fetchRow($select);
	    if (empty($user)) {
	    	return 'Sorry, failed to share this account because the other user is disabled in system.';
	    }
	    
	   	$select  = $this->db->select()->from('acc_client_service')
	    			->where('client_id = ?', $user['client_id'])
	    			->where('share_from = ?', $client_id)
	    			->where('dpt_id = ?', $dpt_id)
	    			->where('dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchRow($select);
	    if (!empty($service)) {
	    	return 'Sorry, you already shared the account to this user.';
	    }
	    	    	    
	    $select  = $this->db->select()->from('acc_client_service')
	    			->where('client_id = ?', $client_id)
	    			->where('share_from = ?', '')
	    			->where('dpt_id = ?', $dpt_id)
	    			->where('dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchRow($select);
	    if (empty($service)) {
	    	return 'Sorry, failed to share this account because you are not the orignal owner.';
	    }
	    
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
    
	    $to_client_id = $user['client_id'];

	    $this->db->beginTransaction();
		try {
			//Zend_Debug::dump($new_client);
			
			$dpt_account_login = $service['dpt_account_login'];
			$login_area = $service['login_area'];
			
			// only emailserving's password need to be stored
			if (isset($service['password']) && $dpt_id == 3006) $dpt_password = $service['password'];
			else $dpt_password = "";
			
			$sql = "INSERT IGNORE INTO acc_client_service (
									`link_id` ,
									`client_id` ,
									`dpt_id` ,
									`dpt_account_id` ,
									`dpt_account_login`,
									`dpt_password`,
									`login_area`,
									`share_from`,
									`create_time` ,
									`update_time` 
									)
									VALUES (
									NULL , '$to_client_id', '$dpt_id', '$dpt_account_id', '$dpt_account_login', '$dpt_password', '$login_area', '$client_id', '$now', '$now')";

			$this->db->query($sql);
			$this->db->commit();
			
			//for action log
			$data=array();
			$data['ip'] = Model_ActionLog::getIp();			
			$data['user_id'] = "";
			$data['client_id'] = $client_id;
			$data['dpt_id'] = $dpt_id;
			$data['dpt_account_id'] = $dpt_account_id;
			$data['action'] = 'share service';
			$data['memo'] = $share_to;			
			Model_ActionLog::actionLog($data);
			
			$select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id);
	    	$owner = $this->db->fetchRow($select);
			$body = "Hi,<br><br>
							A YesUp user ".$owner['email']." has shared an account with you!<br><br>
							You can access the account after you login. <br><br>
							
							Please feel free to contact us if you have any questions.<br><br>
							
							Best regards,<br><br>
							
							YesUp Account Support Team<br>";
    			
			    $mail = new Zend_Mail('UTF-8');
			    $mail->setBodyHtml($body)
			         ->setFrom('no-reply@yesup.com', 'YesUp Account')
			         ->addTo($share_to, '')
			         ->setSubject("A YesUp user has shared his account with you!")
			         ->send();    			
			
		}
		catch (Zend_Exception $e){
			$this->db->rollBack();
			//throw new Zend_Exception("Transaction failed, and roll back.");
			
			//error_log("Transaction failed, and roll back.");
			return 'Database failed';
		}  
		//App_Log::query($this->db);
		$select  = $this->db->select()->from('acc_client_service')
	    			->where('client_id = ?', $to_client_id)
	    			->where('share_from = ?', $client_id)
	    			->where('dpt_id = ?', $dpt_id)
	    			->where('dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchRow($select);
	    if (!$service) {
	    	return 'Database failed';
	    }
	    
	    return $service;
	}

	public function getClientShareService($client_id, $dpt_id, $dpt_account_id)
	{
		$select  = $this->db->select()->from(array('a'=>'acc_client_service'))
					->joinLeft(array('c'=>'acc_client'),'a.client_id=c.client_id',array('email'))
	    			->where('a.share_from = ?', $client_id)
	    			->where('a.dpt_id = ?', $dpt_id)
	    			->where('a.dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchAll($select);
	    if (!$service) {
	    	return false;
	    }
	    
	    return $service;		
	}

	
	public function removeClientShareService($client_id, $dpt_id, $dpt_account_id,$share_from)
	{
	    $select  = $this->db->select()->from('acc_client_service')
	    			->where('client_id = ?', $share_from)
	    			->where('share_from = ?', '')
	    			->where('dpt_id = ?', $dpt_id)
	    			->where('dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchRow($select);
	    if (!$service) {
	    	return false;
	    }		
		
		$sql = " delete from acc_client_service where client_id='$client_id' and dpt_id='$dpt_id' and dpt_account_id='$dpt_account_id' and share_from='$share_from' ";
		$this->db->query($sql);
		
		//for action log
		$data=array();
		$data['ip'] = Model_ActionLog::getIp();			
		$data['user_id'] = "";
		$data['client_id'] = $share_from;
		$data['dpt_id'] = $dpt_id;
		$data['dpt_account_id'] = $dpt_account_id;
		$data['action'] = 'remove share';
		$data['memo'] = $client_id;			
		Model_ActionLog::actionLog($data);
		
		$select  = $this->db->select()->from(array('a'=>'acc_client_service'))
					->joinLeft(array('c'=>'acc_client'),'a.client_id=c.client_id',array('email'))
	    			->where('a.share_from = ?', $share_from)
	    			->where('a.dpt_id = ?', $dpt_id)
	    			->where('a.dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchAll($select);
	    return $service;	
	}
	
	
	public function addShareSecurityKey($client_id, $dpt_id, $dpt_account_id, $to_email, $skey = "")
	{
	    $select  = $this->db->select()->from('acc_security_key');
	    $select->where('client_id = ?', $client_id);
	    $select->where('dpt_id = ?', $dpt_id);
	    $select->where('dpt_account_id = ?', $dpt_account_id);
	    $select->where('to_email = ?', $to_email);
	    
	    $row = $this->db->fetchRow($select);
	    if (!empty($row)){
	    	return $row['security_key'];
	    }

		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
	    
		$query = "insert into acc_security_key (client_id,dpt_id,dpt_account_id,to_email,security_key,create_time) 
					values ('$client_id', '$dpt_id', '$dpt_account_id', '$to_email', '$skey','$now')"; 
		$this->db->query($query);
		if ($this->db->lastInsertId())
			return $skey;
		else 
			return 0; //failed to insert
	}

	public function removeShareSecurityKey($to_email, $skey)
	{
		$query = "delete from acc_security_key where to_email='$to_email' and security_key='$skey' "; 
		$this->db->query($query);
	}
	

	public function signupAddShareService($client_id,$dpt_id,$dpt_account_id,$from_client_id)
	{
	    $select  = $this->db->select()->from('acc_client_service')
	    			->where('client_id = ?', $from_client_id)
	    			->where('share_from = ?', '')
	    			->where('dpt_id = ?', $dpt_id)
	    			->where('dpt_account_id = ?', $dpt_account_id);
	    $service = $this->db->fetchRow($select);
	    if (empty($service)) {
	    	return false;
	    }
	    
		$date = new Zend_Date();
		$now = $date->toString('YYYY-MM-dd H:m:s') ;
	    
	    $dpt_account_login = $service['dpt_account_login'];
		$login_area = $service['login_area'];
		
		// only emailserving's password need to be stored
		if (isset($service['password']) && $dpt_id == 3006) $dpt_password = $service['password'];
		else $dpt_password = "";
		
		$sql = "INSERT IGNORE INTO acc_client_service (
								`link_id` ,
								`client_id` ,
								`dpt_id` ,
								`dpt_account_id` ,
								`dpt_account_login`,
								`dpt_password`,
								`login_area`,
								`share_from`,
								`create_time` ,
								`update_time` 
								)
								VALUES (
								NULL , '$client_id', '$dpt_id', '$dpt_account_id', '$dpt_account_login', '$dpt_password', '$login_area', '$from_client_id', '$now', '$now')";
		
		$this->db->query($sql);
		
		if ($this->db->lastInsertId())
		{
			//for action log
			$data=array();
			$data['ip'] = Model_ActionLog::getIp();			
			$data['user_id'] = "";
			$data['client_id'] = $from_client_id;
			$data['dpt_id'] = $dpt_id;
			$data['dpt_account_id'] = $dpt_account_id;
			$data['action'] = 'signup share service';
			$data['memo'] = $client_id;			
			Model_ActionLog::actionLog($data);
			return true;
		}else{
			return false;
		}		
	}	
	
	
	public function getShareInfo($email,$key)
	{
	    $select  = $this->db->select()->from('acc_security_key');
	    $select->where('to_email = ?', $email);
	    $select->where('security_key = ?', $key);
	    
	    $row = $this->db->fetchRow($select);
	    return $row;
	}
	
	
	public function getManager($client_id)
	{
	    $select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id);
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return array();
	    }
		
	    $select  = $this->db->select()->from('acc_managers')->where('client_id = ?', $client_id);
	    $manager = $this->db->fetchRow($select);
		
	    if (!$manager) {
	    	$user['dpt_ids']=array();
	    }else{				
	    	$user['dpt_ids']=array();
	    	$dpt_ids=explode(',',$manager['dpt_ids']);
	    	$user['dpt_ids'] = $dpt_ids;
	    }
	    return $user;
	}
	
	
	public function setManager($client_id,$dpt_ids_str)
	{
	    $select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id);
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return false;
	    }
		
		$sql = "update acc_client set role='manager' where client_id='$client_id' ";
		$this->db->query($sql);
	    
	    $select  = $this->db->select()->from('acc_managers')->where('client_id = ?', $client_id);
	    $manager = $this->db->fetchRow($select);
		
	    if (!$manager) {
		    $query = "insert into acc_managers (client_id,dpt_ids) values ('$client_id', '$dpt_ids_str')"; 
			$this->db->query($query);
			if ($this->db->lastInsertId())
				return true;
			else 
				return false; //failed to insert
	    }else{				
			$sql = " update acc_managers set dpt_ids='$dpt_ids_str' where client_id='$client_id' ";
			$this->db->query($sql);
			return true;
	    }
	}

	public function unsetManager($client_id)
	{
	    $select  = $this->db->select()->from('acc_client')->where('client_id = ?', $client_id);
	    $user = $this->db->fetchRow($select);
	    if (!$user) {
	    	return false;
	    }
		
		$sql = "update acc_client set role='client' where client_id='$client_id' ";
		$this->db->query($sql);
	    
		$sql = "delete from acc_managers  where client_id='$client_id' ";
		$this->db->query($sql);
		return true;
	}
	
	public function getManagerClientAuth($manager_id,$client_id)
	{
		$managerInfo = $this->getManager($manager_id);
		$clientInfo = $this->getClientService($client_id);
		foreach($clientInfo as $key => $item ){
			if (in_array($item['dpt_id'],$managerInfo['dpt_ids'])){
				return true;
			}
		}
		return false;				
	}	
	
	public function getNetworkClientByKey($client_id, $partner_key)
	{
	    $select  = $this->db->select()->from('acc_client');
	    $select->where('client_id = ?', $client_id);
	    $select->where('partner_key = ?', $partner_key);
	
	    if (!empty($partner_key)) $row = $this->db->fetchRow($select);
	    else $row = false;
	    return $row;
	}
	
	
	public static function str_makerand ($minlength, $maxlength) {
	    $charset = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"; 
	    if ($minlength > $maxlength) 
	        $length = mt_rand ($maxlength, $minlength); 
	    else
	        $length = mt_rand ($minlength, $maxlength); 
	        
	    $key='';
	    for ($i=0; $i<$length; $i++) $key .= $charset[(mt_rand(0,(strlen($charset)-1)))]; 
	    return $key; 
	} 			
	
	public static function isValidEmail($email){
		if(preg_match('/^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$/', $email)) {
			return true; 
		} 
		else {  
			return false; 
		}  
	}	
	
}